package com.rocket.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;

import com.rocket.entity.Work;

public class WorkSql {

	private static WorkSql	instance						= null;
	private final String	TABLE_NAME						= "Work";

	private final String	KEY_WORK_ID						= "id";
	private final String	KEY_WORK_NAME					= "name";				//角度
	private final String	KEY_WORK_NUMBER					= "number";
	private final String	KEY_WORK_FIXED_LONGITUDE		= "fixedlongitude";
	private final String	KEY_WORK_FIXED_LATITUDE			= "fixedlatitude";
	private final String	KEY_WORK_MOVE_LONGITUDE			= "movelongitude";
	private final String	KEY_WORK_MOVE_LATITUDE			= "movelatitude";
	private final String	KEY_WORK_FIREARM_TYPE			= "firearmtype";
	private final String 	KEY_WORK_HAIBA = "haiba";
	private final String	KEY_WORK_CLOUD_TYPE				= "cloudtype";
	private final String	KEY_WORK_CLOUD_TOP_HEIGHT		= "cloudtopheight";
	private final String	KEY_WORK_CLOUD_BOTTOM_HEIGHT	= "cloudbottomheight";
	private final String	KEY_WORK_CLOUD_CENTER_HEIGHT		= "cloudcenterheight";
	private final String	KEY_WORK_CLOUD_6_HEIGHT	= "cloud6height";
	private final String 	KEY_WORK_ROCKET_ANGLE = "rocketangle";
	private final String	KEY_WORK_ROCKET_MAX_HEIGHT		= "rocketmaxheight";
	private final String	KEY_WORK_ROCKET_MAX_LENGTH		= "rocketmaxlength";
	private final String 	KEY_WORK_MISSILE_ANGLE = "missileangle";
	private final String	KEY_WORK_MISSILE_MAX_HEIGHT		= "missilemaxheight";
	private final String	KEY_WORK_MISSILE_MAX_LENGTH		= "missilemaxlength";
	private final String	KEY_WORK_MISSILE_TIME			= "misseletime";
	private final String 	KEY_WORK_CREATE_TIME = "createtime";

	public static synchronized WorkSql getInstance() {
		if (null == instance) {
			instance = new WorkSql();
		}
		return instance;
	}

	public void init() {
		createTable();
		initData();
	}

	private void createTable() {
		if (DataBaseOpenHelper.mDataBase != null) {
			String sql = "drop table if exists " + TABLE_NAME;
			DataBaseOpenHelper.mDataBase.execSQL(sql);
			sql = "create table if not exists " + TABLE_NAME + "( " + KEY_WORK_ID + " INTEGER PRIMARY KEY,"
					+ KEY_WORK_NAME + " VARCHAR," + KEY_WORK_NUMBER + " VARCHAR,"
					+ KEY_WORK_FIXED_LONGITUDE + " VARCHAR," + KEY_WORK_FIXED_LATITUDE + " VARCHAR,"
					+ KEY_WORK_MOVE_LONGITUDE + " VARCHAR," + KEY_WORK_MOVE_LATITUDE + " VARCHAR,"
					+ KEY_WORK_HAIBA + " INTEGER,"+ KEY_WORK_CLOUD_CENTER_HEIGHT + " INTEGER,"+ KEY_WORK_CLOUD_6_HEIGHT + " INTEGER,"
					+ KEY_WORK_FIREARM_TYPE + " INTEGER," + KEY_WORK_CLOUD_TYPE + " VARCHAR,"
					+ KEY_WORK_CLOUD_TOP_HEIGHT + " INTEGER," + KEY_WORK_CLOUD_BOTTOM_HEIGHT + " INTEGER,"
					+ KEY_WORK_ROCKET_ANGLE + " INTEGER,"+ KEY_WORK_ROCKET_MAX_HEIGHT + " INTEGER," + KEY_WORK_ROCKET_MAX_LENGTH + " INTEGER,"
					+ KEY_WORK_MISSILE_ANGLE + " INTEGER,"+ KEY_WORK_MISSILE_MAX_HEIGHT + " INTEGER," + KEY_WORK_MISSILE_MAX_LENGTH + " INTEGER,"
					+ KEY_WORK_MISSILE_TIME + " INTEGER,"+KEY_WORK_CREATE_TIME+" INTEGER)";
			DataBaseOpenHelper.mDataBase.execSQL(sql);
		}
	}

	public long insert(Work work) {
		if (DataBaseOpenHelper.mDataBase != null) {
			ContentValues values = new ContentValues();
			values.put(KEY_WORK_NAME, work.name);
			values.put(KEY_WORK_NUMBER, work.number);
			values.put(KEY_WORK_FIXED_LONGITUDE, work.fixedLongitude);
			values.put(KEY_WORK_FIXED_LATITUDE, work.fixedLatitude);
			values.put(KEY_WORK_MOVE_LONGITUDE, work.moveLongitude);
			values.put(KEY_WORK_MOVE_LATITUDE, work.moveLatitude);
			values.put(KEY_WORK_FIREARM_TYPE, work.firearmType);
			values.put(KEY_WORK_HAIBA, work.haiba);
			values.put(KEY_WORK_CLOUD_CENTER_HEIGHT, work.cloudCenterHeight);
			values.put(KEY_WORK_CLOUD_6_HEIGHT, work.cloud6Height);
			values.put(KEY_WORK_CLOUD_TYPE, work.cloudType);
			values.put(KEY_WORK_CLOUD_TOP_HEIGHT, work.cloudTopHeight);
			values.put(KEY_WORK_CLOUD_BOTTOM_HEIGHT, work.cloudBottomHeight);
			values.put(KEY_WORK_ROCKET_ANGLE, work.rocketAngle);
			values.put(KEY_WORK_ROCKET_MAX_HEIGHT, work.rocketMaxHeight);
			values.put(KEY_WORK_ROCKET_MAX_LENGTH, work.rocketMaxLength);
			values.put(KEY_WORK_MISSILE_ANGLE, work.missileAngle);
			values.put(KEY_WORK_MISSILE_MAX_HEIGHT, work.missileMaxHeight);
			values.put(KEY_WORK_MISSILE_MAX_LENGTH, work.missileMaxLength);
			values.put(KEY_WORK_MISSILE_TIME, work.misseleTime);
			values.put(KEY_WORK_CREATE_TIME, work.createTime);
			return DataBaseOpenHelper.mDataBase.insert(TABLE_NAME, null, values);
		}
		return 0;
	}

	public long update(Work work) {
		if (DataBaseOpenHelper.mDataBase != null) {
			ContentValues values = new ContentValues();
			values.put(KEY_WORK_NAME, work.name);
			values.put(KEY_WORK_NUMBER, work.number);
			values.put(KEY_WORK_FIXED_LONGITUDE, work.fixedLongitude);
			values.put(KEY_WORK_FIXED_LATITUDE, work.fixedLatitude);
			values.put(KEY_WORK_MOVE_LONGITUDE, work.moveLongitude);
			values.put(KEY_WORK_MOVE_LATITUDE, work.moveLatitude);
			values.put(KEY_WORK_FIREARM_TYPE, work.firearmType);
			values.put(KEY_WORK_HAIBA, work.haiba);
			values.put(KEY_WORK_CLOUD_CENTER_HEIGHT, work.cloudCenterHeight);
			values.put(KEY_WORK_CLOUD_6_HEIGHT, work.cloud6Height);
			values.put(KEY_WORK_CLOUD_TYPE, work.cloudType);
			values.put(KEY_WORK_CLOUD_TOP_HEIGHT, work.cloudTopHeight);
			values.put(KEY_WORK_CLOUD_BOTTOM_HEIGHT, work.cloudBottomHeight);
			values.put(KEY_WORK_ROCKET_ANGLE, work.rocketAngle);
			values.put(KEY_WORK_ROCKET_MAX_HEIGHT, work.rocketMaxHeight);
			values.put(KEY_WORK_ROCKET_MAX_LENGTH, work.rocketMaxLength);
			values.put(KEY_WORK_MISSILE_ANGLE, work.missileAngle);
			values.put(KEY_WORK_MISSILE_MAX_HEIGHT, work.missileMaxHeight);
			values.put(KEY_WORK_MISSILE_MAX_LENGTH, work.missileMaxLength);
			values.put(KEY_WORK_MISSILE_TIME, work.misseleTime);
			values.put(KEY_WORK_CREATE_TIME, work.createTime);
			return DataBaseOpenHelper.mDataBase.update(TABLE_NAME, values, KEY_WORK_ID + " = ?", new String[] { String.valueOf(work.id) });
		}
		return 0;
	}

	public void delete(Work work) {
		delete(work.id);
	}

	public void delete(int id) {
		if (DataBaseOpenHelper.mDataBase != null) {
			DataBaseOpenHelper.mDataBase.delete(TABLE_NAME, KEY_WORK_ID + " = ?", new String[] { String.valueOf(id) });
		}
	}

	public ArrayList<Work> query() {
		ArrayList<Work> works = new ArrayList<Work>();
		if (DataBaseOpenHelper.mDataBase != null) {
			Cursor cur = null;
			try {
				DataBaseOpenHelper.mDataBase.beginTransaction();
				cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null, null, null, null, null, null);
				if (cur != null && cur.moveToFirst()) {
					do {
						Work work = new Work();
						work.id = cur.getInt(cur.getColumnIndex(KEY_WORK_ID));
						work.name = cur.getString(cur.getColumnIndex(KEY_WORK_NAME));
						work.number = cur.getString(cur.getColumnIndex(KEY_WORK_NUMBER));
						work.fixedLongitude = cur.getString(cur.getColumnIndex(KEY_WORK_FIXED_LONGITUDE));
						work.fixedLatitude = cur.getString(cur.getColumnIndex(KEY_WORK_FIXED_LATITUDE));
						work.moveLongitude = cur.getString(cur.getColumnIndex(KEY_WORK_MOVE_LONGITUDE));
						work.moveLatitude = cur.getString(cur.getColumnIndex(KEY_WORK_MOVE_LATITUDE));
						work.firearmType = cur.getInt(cur.getColumnIndex(KEY_WORK_FIREARM_TYPE));
						work.haiba  = cur.getInt(cur.getColumnIndex(KEY_WORK_HAIBA));
						work.cloudCenterHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_CENTER_HEIGHT));
						work.cloud6Height = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_6_HEIGHT));
						work.cloudType = cur.getString(cur.getColumnIndex(KEY_WORK_CLOUD_TYPE));
						work.cloudTopHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_TOP_HEIGHT));
						work.cloudBottomHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_BOTTOM_HEIGHT));
						work.rocketAngle = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_ANGLE));
						work.rocketMaxHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_MAX_HEIGHT));
						work.rocketMaxLength = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_MAX_LENGTH));
						work.missileAngle = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_ANGLE));
						work.missileMaxHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_MAX_HEIGHT));
						work.missileMaxLength = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_MAX_HEIGHT));
						work.misseleTime = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_TIME));
						work.createTime = cur.getLong(cur.getColumnIndex(KEY_WORK_CREATE_TIME));
						works.add(work);
					} while (cur.moveToNext());
				}
				DataBaseOpenHelper.mDataBase.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (cur != null) {
					cur.close();
					cur = null;
				}
				DataBaseOpenHelper.mDataBase.endTransaction();
			}
		}
		return works;
	}

	private void initData() {

	}
	
	public ArrayList<Work> query(String name,long start,long end,ArrayList<Integer> types) {
		ArrayList<Work> works = new ArrayList<Work>();
		if (DataBaseOpenHelper.mDataBase != null) {
			Cursor cur = null;
			try {
				DataBaseOpenHelper.mDataBase.beginTransaction();
				StringBuilder selection = new StringBuilder();
				ArrayList<String> selectionArgs = new ArrayList<String>();
				if(name != null && !name.equals("")){
					selection.append(KEY_WORK_NAME + " = ? ");
					selectionArgs.add(name);
				}
				if(end > 0){
					if(selectionArgs.size()>0){
						selection.append(" and ");
					}
					selection.append(KEY_WORK_CREATE_TIME +" >= ? and "+KEY_WORK_CREATE_TIME+"  <= ? ");
					selectionArgs.add(String.valueOf(start));
					selectionArgs.add(String.valueOf(end));
				}
				
				if(types != null && types.size() >0){
					if(selectionArgs.size()>0){
						selection.append(" and ( ");
					}
					for(int i=0;i<types.size();i++){
						selection.append(KEY_WORK_FIREARM_TYPE + " = ? ");
						selectionArgs.add(String.valueOf(types.get(i)));
						if(i<types.size()-1){
							selection.append(" or ");
						}
					}
					if(types.size() < selectionArgs.size()){
						selection.append(" ) ");
					}
				}
				if(selectionArgs.size()>0){
					String[] args = new String[selectionArgs.size()];
					for(int i=0;i<selectionArgs.size();i++){
						args[i] = selectionArgs.get(i);
					}
					cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null,
							selection.toString(), args, null, null, null);
				}else{
					cur = DataBaseOpenHelper.mDataBase.query(TABLE_NAME, null, null,null, null, null, null);
				}
				if (cur != null && cur.moveToFirst()) {
					do {
						Work work = new Work();
						work.id = cur.getInt(cur.getColumnIndex(KEY_WORK_ID));
						work.name = cur.getString(cur.getColumnIndex(KEY_WORK_NAME));
						work.number = cur.getString(cur.getColumnIndex(KEY_WORK_NUMBER));
						work.fixedLongitude = cur.getString(cur.getColumnIndex(KEY_WORK_FIXED_LONGITUDE));
						work.fixedLatitude = cur.getString(cur.getColumnIndex(KEY_WORK_FIXED_LATITUDE));
						work.moveLongitude = cur.getString(cur.getColumnIndex(KEY_WORK_MOVE_LONGITUDE));
						work.moveLatitude = cur.getString(cur.getColumnIndex(KEY_WORK_MOVE_LATITUDE));
						work.firearmType = cur.getInt(cur.getColumnIndex(KEY_WORK_FIREARM_TYPE));
						work.haiba  = cur.getInt(cur.getColumnIndex(KEY_WORK_HAIBA));
						work.cloudCenterHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_CENTER_HEIGHT));
						work.cloud6Height = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_6_HEIGHT));
						work.cloudType = cur.getString(cur.getColumnIndex(KEY_WORK_CLOUD_TYPE));
						work.cloudTopHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_TOP_HEIGHT));
						work.cloudBottomHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_CLOUD_BOTTOM_HEIGHT));
						work.rocketAngle = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_ANGLE));
						work.rocketMaxHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_MAX_HEIGHT));
						work.rocketMaxLength = cur.getInt(cur.getColumnIndex(KEY_WORK_ROCKET_MAX_LENGTH));
						work.missileAngle = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_ANGLE));
						work.missileMaxHeight = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_MAX_HEIGHT));
						work.missileMaxLength = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_MAX_HEIGHT));
						work.misseleTime = cur.getInt(cur.getColumnIndex(KEY_WORK_MISSILE_TIME));
						work.createTime = cur.getLong(cur.getColumnIndex(KEY_WORK_CREATE_TIME));
						works.add(work);
					} while (cur.moveToNext());
				}
				DataBaseOpenHelper.mDataBase.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (cur != null) {
					cur.close();
					cur = null;
				}
				DataBaseOpenHelper.mDataBase.endTransaction();
			}
		}
		return works;
	}
}
